﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using System.Configuration;

namespace DataLayer
{
    /// <summary>
    /// Base Class for making setting up and making connections to the database
    /// </summary>
    public abstract class BaseDL
    {
        /// <summary>
        /// Connection string
        /// </summary>
        protected string DSN { get { return ConfigurationManager.ConnectionStrings["LocalSQLServer"].ConnectionString; } }
        protected SqlConnection _conn;
        protected SqlCommand _cmd;
        protected SqlDataAdapter _adapter;
        protected SqlParameter[] _params;
        protected DataTableMapping[] _dtMaping;
        protected string _storedProcedureName;

        private  DataSet _ds;
        public BaseDL()
        {

        }
        /// <summary>
        /// used to fill datasets
        /// </summary>
        private  void fill()
        {
            try
            {
                this._ds = new DataSet();
                this._conn = new SqlConnection(this.DSN);
                this._cmd = new SqlCommand(this._storedProcedureName, this._conn);
                this._cmd.CommandType = CommandType.StoredProcedure;
                if (this._params != null)
                {
                    this._cmd.Parameters.AddRange(this._params);
                }
                this._adapter = new SqlDataAdapter(this._cmd);
                if (this._dtMaping != null)
                {
                    this._adapter.TableMappings.AddRange(this._dtMaping);
                }
                this._conn.Open();
                this._adapter.Fill((DataSet)this._ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this._conn != null)
                {
                    this._conn.Close();
                    this._conn.Dispose();
                }

                if (this._cmd != null)
                    this._cmd.Dispose();

                if (this._adapter != null)
                    this._adapter.Dispose();

            }
        }
        /// <summary>
        /// Executes query and returns the rows affected used for inserts and updates
        /// </summary>
        /// <returns></returns>
        protected int executeProcedure()
        {
            try
            {
                this._conn = new SqlConnection(this.DSN);
                this._cmd = new SqlCommand(this._storedProcedureName, this._conn);
                this._cmd.CommandType = CommandType.StoredProcedure;
                if (this._params != null)
                {
                    this._cmd.Parameters.AddRange(this._params);
                }
                this._adapter = new SqlDataAdapter(this._cmd);
                
                this._conn.Open();
                return this._adapter.SelectCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this._conn != null)
                {
                    this._conn.Close();
                    this._conn.Dispose();
                }

                if (this._cmd != null)
                    this._cmd.Dispose();

                if (this._adapter != null)
                    this._adapter.Dispose();

            }
        }
        /// <summary>
        /// This method calls fill method internally and returns the dataset
        /// </summary>
        /// <returns>Dataset</returns>
        protected DataSet getDataSet()
        {
            this.fill();
            return this._ds;
        }

    }
}
